﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;

using Visio = Microsoft.Office.Interop.Visio;
using Excel = Microsoft.Office.Interop.Excel;

using System.Data.SqlClient;


namespace VisioAddInExcel
{
    public partial class ThisAddIn
    {


        static void Main(string[] args)
        {
            Console.WriteLine("Input Username: ");
            string username = Console.ReadLine();
            Console.WriteLine("Input Password: ");
            string password = Console.ReadLine();
            SqlConnection dataConnection = new SqlConnection();
            try
            {
                string connstring = String.Format("User ID={0};Password={1};Initial Catalog = TIF SQL;" +
                    "Data Source=10.0.0.52\\TIFSQL", username, password);
                dataConnection.ConnectionString = connstring;
                dataConnection.Open();
            }
            catch(SqlException e)
            {
                Console.WriteLine("Error accessing database: {0}", e.Message);
            }
        }
        private static string _DocumentName = string.Empty;

        internal static void GenerateBOM(
            string drawingFileName,
            string excelFileName)
        {
            //Get the name of the Visio Document to be opened
            _DocumentName =
                System.IO.Path.GetFileNameWithoutExtension(drawingFileName);

            //Read the information from the drawing
            readVisioDrawing(drawingFileName);

            //create the Excel File.
            createExcelFile(excelFileName);
        }

        private static Hashtable _PartNameQuantity = new Hashtable();

        private static void readVisioDrawing(
            string drawingFileName)
        {
            //Open the drawing in Visio
            Visio.Application visioApplication = new Visio.Application();
            Visio.Document partsDocument = visioApplication.Documents.Open(drawingFileName);

            //Gather the information from the drawing.
            _PartNameQuantity.Clear();
            foreach (Visio.Page thisPage in partsDocument.Pages)
            {
                foreach(Visio.Shape thisShape in thisPage.Shapes)
                {
                    processDrawingShape(thisShape);
                }
            }
            //Close the drawing
            visioApplication.Quit();
        }

        private const string _VisioPartNameCell = "User.PartName";
        private const string _VisioPropCellPrefix = "Prop.";
        //private static Hashtable _PartNameQuantity = new Hashtable();

        private static void processDrawingShape(
            Visio.Shape thisShape)
        {

            try
            {
                //Get the part name from the shape
                string partName = thisShape.get_Cells(_VisioPartNameCell).
                get_ResultStr((short)Visio.VisUnitCodes.visNoCast).ToUpper();


                //Reflect this shape in the part name/quantity table
                if (_PartNameQuantity.Contains(partName))
                    _PartNameQuantity[partName] = (int)_PartNameQuantity[partName] + 1;
                else
                    _PartNameQuantity.Add(partName, 1);
            }
            catch
            {
                //Ignore shapes without a part name

            }

            //Process any subshapes within a group shape
            foreach(Visio.Shape thisSubShape in thisShape.Shapes)
            {
                processDrawingShape(thisSubShape);
            }
        }

        private const string _ExcelTemplateName = "ExcelBOM.xls";

        private static void createExcelFile(
            string excelFileName)
        {
            //Copy the Excel file template to the new filename.
            System.IO.File.Copy("C:\\Documents and Settings\\Administrator\\Application Data\\Microsoft\\Excel\\" + "\\" + _ExcelTemplateName,
                excelFileName, true);
       

            //Update the parts table information
            updatePartsTable(excelFileName);

            //Update the document information

            updateDocumentInformation(excelFileName);
        }
        private const string _TIFSQLConnectionString = "Driver={SQL Server Native Client 10.0};Server = 10.0.0.20;Database = TIF SQL;Uid=David.Gitz; Pwd = 79331982'";
        private const string _PartsTableConnectionString =
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes\" ";
        private const string _PartsTableName = "PartNameQuantity";
        private const string _PartsTableNameColumn = "Part Name";
        private const string _PartsTableQuantityColumn = "Quantity";
        private const string _PartsTableSelectString = "Select * From [{0}]";
        private const string _PartsTableUpdateString =
            "UPDATE [{0}] SET [{1}] = ? WHERE [{2}]";

        private static void updatePartsTable(
            string excelFileName)
        {
            // Open the data portion of the Excel file as a dataset
            OleDbConnection connection = new OleDbConnection(
                string.Format(_PartsTableConnectionString, excelFileName));
            connection.Open();

            //Access the part quantity table
            OleDbDataAdapter partQuantityAdapter = new OleDbDataAdapter(
                string.Format(_PartsTableSelectString, _PartsTableName),
                connection);
            DataSet partQuantityDataset = new DataSet();
            partQuantityAdapter.Fill(partQuantityDataset, _PartsTableName);
            if (partQuantityDataset.Tables.Count > 0)
            {
                //Update the records with the quantities from the drawing.
                foreach (DataRow thisRow in partQuantityDataset.Tables[0].Rows)
                {
                    string partName = thisRow[_PartsTableNameColumn].ToString().ToUpper();
                    if(_PartNameQuantity.Contains(partName))
                        thisRow[_PartsTableQuantityColumn] = (int)_PartNameQuantity[partName];
                    else
                        thisRow[_PartsTableQuantityColumn] = 0;
                }

                //Generate the update command
                partQuantityAdapter.UpdateCommand = new OleDbCommand(
                    string.Format(_PartsTableUpdateString, _PartsTableName,
                    _PartsTableQuantityColumn, _PartsTableNameColumn), connection);
                partQuantityAdapter.UpdateCommand.Parameters.Add(
                    "@" + _PartsTableQuantityColumn, OleDbType.Numeric).
                SourceColumn = _PartsTableQuantityColumn;
                partQuantityAdapter.UpdateCommand.Parameters.Add(
                    "@" + _PartsTableNameColumn, OleDbType.VarChar, 255).
                SourceColumn = _PartsTableNameColumn;

                //Push the updated data back to Excel
                partQuantityAdapter.Update(partQuantityDataset, _PartsTableName);
            }
            connection.Close();
        }

        private const string _DocumentNameCell = "DocumentName";
        private const string _DocumentDateCell = "DocumentDate";

        private static void updateDocumentInformation(string excelFileName)
        {
            object optional = System.Reflection.Missing.Value;

            try
            {
                //Open the Excel file and use the Excel API
                //Excel stays hidden during the process

                Excel.Application excelApplication = new Excel.ApplicationClass();

                Excel.Workbook excelWorkbook =
                    excelApplication.Workbooks.Open(excelFileName,
                    optional, optional, optional, optional, optional,
                    optional, optional, optional, optional, optional,
                    optional, optional, optional, optional);

                //Update the records with the values from the drawing.
                Excel.Worksheet excelWorksheet =
                    (Excel.Worksheet)excelWorkbook.Worksheets[1];
                excelWorksheet.get_Range(_DocumentNameCell, optional).
                    Value2 = _DocumentName;
                excelWorksheet.get_Range(_DocumentDateCell, optional).
                Value2 = System.DateTime.Today;

                //Save and Close
                excelWorkbook.Save();
                excelWorkbook.Close(false, optional, optional);
                excelApplication.Quit();

                    
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }
        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }

}
